#
#用于修正允许零库存和负库存出货早期程序bug带来的问题数据
#零库存商品直接出货，除了库存数量外成本和价格数据斗没有
#
#需要在.bahs_profile设置环境变量
#export PSI_PASSWORD=xxxxxxx
#export RDS_AGENT=yyyy
#
#手工在命令行运行 ruby fix_inventory_detail.rb
#
require 'mysql2'

    rds = Mysql2::Client.new(:host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1401', :password => ENV['PSI_PASSWORD'])

    goodsName = {};
    goodsPrice = {};
    goodsCategory = {};
    goodsCode = {};
    sql = "select * from psi.t_goods"
    res = rds.query(sql)
    res.each do |res|
        id = res['id'];
        name = res['name'];
        purchase_price = res['purchase_price'];
        category_id = res['category_id'];
        code = res['code'];
        goodsName.store(id,name);
        goodsPrice.store(id,purchase_price);
        goodsCode.store(id,code);
        goodsCategory.store(id,category_id);
    end
    #goodsName.each { |id,name| puts "#{id} #{name}" }


    #在收发明细表上曾有零出库或入库成本单价，零入库或出库数量的商品列表
    questionGoods = {};
	sql = "select distinct goods_id from psi.t_inventory_detail where 
		( balance_count <= 0 or balance_price = 0 or in_count = 0 or in_price= 0 or out_price = 0 )
		and warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963'
	"
    res = rds.query(sql)
    res.each do |res|
        gid = res['goods_id'];
        price = goodsPrice[gid];
        questionGoods.store(gid,price);
    end
    puts "===questioned goods total: #{questionGoods.count}"
    #questionGoods.each { |gid, price| puts "#{goodsName[gid]} #{gid} #{sprintf("%.2f", price)}" }

    questionGoods.each { |gid, suggest_price| 

        #only fix some goods
        #if ( goodsCode[gid] != 'xxx' ) then
       	if ( goodsCode[gid]=='20008880') then 

            #先重置库存总帐上的商品项目
            sqlu = "update psi.t_inventory set balance_count=0.0, balance_money=0.0, balance_price=#{sprintf('%.3f',suggest_price)} where goods_id='#{gid}' and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963' "
            puts sqlu
            resu = rds.query(sqlu)

            puts "example goods_id: #{gid}"
            sql2 = "select * from psi.t_inventory_detail where warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and goods_id='#{gid}' order by id"
            res2 = rds.query(sql2)

            res2.each do |r2|

                #get latest balance

                inv_detail_id = r2['id']
                #get latest balance
                sql3 = "select * from psi.t_inventory where warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and goods_id='#{gid}' order by id"
                res3 = rds.query(sql3)
                b_money = res3.first['balance_money']
                b_count = res3.first['balance_count']
                b_price = suggest_price #this can be removed

                if r2['in_count'].nil? then

                    #出库记录
                    #fix current record
                    out_money = r2['out_money']
                    out_count = r2['out_count']
                    out_price = r2['out_price']
                    if out_price <=0 then out_price = suggest_price end
                    if out_money <=0 then out_money = out_count * out_price end

                    #recal current balance
                    b_money -= out_money
                    b_count -= out_count
                    if b_count != 0 then b_price = b_money / b_count else b_price = suggest_price end
		

                    sqlu = "update psi.t_inventory_detail set balance_count=#{sprintf('%.8f', b_count)}, balance_money=#{sprintf('%.8f', b_money)}, balance_price=#{sprintf('%.8f', b_price)}, out_money=#{sprintf('%.8f', out_money)}, out_price=#{sprintf('%.3f', out_price)}, fixed='fixed' where id='#{inv_detail_id}'"
                    puts sqlu
                    resu = rds.query(sqlu)
                
                    #update balance
                    sqlu = "update psi.t_inventory set balance_count=#{sprintf('%.8f',b_count)}, balance_money=#{sprintf('%.8f',b_money)}, balance_price=#{sprintf('%.8f',b_price)} where goods_id='#{gid}' and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963' "
                    puts sqlu
                    resu = rds.query(sqlu)

                elsif r2['out_count'].nil? then

                    #入库记录
                    #fix current record
                    in_money = r2['in_money']
                    in_count = r2['in_count']
                    in_price = r2['in_price']
                    if in_price <=0 then in_price = suggest_price end
                    if in_money <=0 then in_money = in_count * in_price end

                    #recal current balance
                    b_money += in_money
                    b_count += in_count
                    if b_count != 0 then b_price = b_money / b_count else b_price = suggest_price end

                    sqlu = "update psi.t_inventory_detail set balance_count=#{sprintf('%.8f', b_count)}, balance_money=#{sprintf('%.8f', b_money)}, balance_price=#{sprintf('%.8f', b_price)}, in_money=#{sprintf('%.8f', in_money)}, in_price=#{sprintf('%.3f', in_price)}, fixed='fixed' where id='#{inv_detail_id}'"
                    puts sqlu
                    resu = rds.query(sqlu)
                
                    #update balance
                    sqlu = "update psi.t_inventory set balance_count=#{sprintf('%.8f',b_count)}, balance_money=#{sprintf('%.8f',b_money)}, balance_price=#{sprintf('%.8f',b_price)} where goods_id='#{gid}' and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963' "
                    puts sqlu
                    resu = rds.query(sqlu)

                else
                    puts "Something wrong"
                end

            end

        end

    }


rds.close
